const mysql = require("mysql");
const db = mysql.createPool({
  host: "127.0.0.1",
  user: "root",
  password: "admin123",
  prot: "3306",
  database: "my_db_01",
});
// 测试数据库连接是否成功
/* db.query("SELECT 1", (err, results) => {
  if (err) console.log(err, "error");
  console.log(results);
}); */

// 查询user表中所有数据
/* const sqlStr = "select * from users";
db.query(sqlStr, (err, results) => {
  // 查询数据失败
  if (err) return console.log(err, message, "error");
  // 查询数据成功
  console.log(results, "查询数据成功");
}); */

// 向users表中插入数据 第一种写法
/* const sqlStr = "insert into users(username,password) values(?,?)";

db.query(sqlStr, ["zhangsan", "123456"], (err, results) => {
  if (err) return console.log(err, "error");
  console.log(results);
}); */
// 向users表中插入数据 第二种写法
/* const sqlStr1 = "insert into users(username,password) values ('小武','888888')";
db.query(sqlStr1, (err, results) => {
  if (err) return console.log(err, "error");
  console.log(results.affectedRows, "插入数据成功");
});
 */
/* const users = {
  username: "lisi",
  password: "999999",
};
const sqlStr2 = "insert into users set ?";
db.query(sqlStr2, users, (err, results) => {
  if (err) return console.log(err, "error");
  console.log(results.affectedRows, "插入数据成功");
}); */

/* const users = {
  id: 5,
  userName: "pinnuo",
  passWord: "888888",
};
const sqlStr2 = "update  users set userName=?,passWord=? where id = ?";
db.query(
  sqlStr2,
  [users.userName, users.passWord, users.id],
  (err, results) => {
    if (err) return console.log(err, "error");
    console.log(results.affectedRows, "更新数据成功");
  }
);
 */
// 更新数据的便捷方式
/* const users = {
  id: 5,
  userName: "上证50",
  passWord: "888888",
};
const sqlStr = "update  users set ? where id = ?";
db.query(sqlStr, [users, users.id], (err, results) => {
  if (err) return console.log(err, "error");
  console.log(results.affectedRows, "更新数据成功");
});
 */

// 删除数据
/* const sqlStr = "delete from users where id=?";
db.query(sqlStr, [34, 35], (err, results) => {
  if (err) return console.log(err, "error");
  console.log(results, "删除数据成功");
});
 */

// 标记删除不是真正删除数据
const sqlStr = "update users set status=? where id=?";
db.query(sqlStr, [99, 4], (err, results) => {
  if (err) return console.log(err?.sqlMessage, "error");
  console.log(results, "删除数据成功");
});
